﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Entity;
using System.Data;

namespace DAL
{
    public class NewsDal:SQLConnection
    {

        public List<News> News_GetById(string Id)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetById", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", Id));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
   
        public List<News> News_GetByHome(string GroupNewsId, string lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByHome", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", GroupNewsId));
                dbCmd.Parameters.Add(new SqlParameter("@lang", lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
   
        public List<News> News_GetByTop(string Top, string Where, string Order)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByTop", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Top", Top));
                dbCmd.Parameters.Add(new SqlParameter("@Where", Where));
                dbCmd.Parameters.Add(new SqlParameter("@Order", Order));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
        public List<News> News_GetByGroupNewsId(string GroupNewsId)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByGroupNewsId", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", GroupNewsId));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
        public List<News> News_GetByKMHangKhong(string lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByKMHangKhong", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Lang", lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
    
        public List<News> News_GetByNewsRelated(string newId, string grnId, string Lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByNewsRelated", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", newId));
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", grnId));
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
     
        public List<News> News_GetByNewsOther(string newId, string grnId, string Lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByNewsOther", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", newId));
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", grnId));
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
    
        public List<News> News_GetByGroupNewsID_Date_Top(string grnid, string date, string top)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByGroupNewsID_Date_Top", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", grnid));
                dbCmd.Parameters.Add(new SqlParameter("@Date", date));
                dbCmd.Parameters.Add(new SqlParameter("@top", top));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
       
        public List<News> News_GetByAll(string Lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByAll", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
     
        public List<News> News_GetByDulich(string Lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByDulich", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
      
        public List<News> News_GetByDulich3(string Lang)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_GetByDulich3", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
      
        public List<News> News_Paging(string CurentPage, string PageSize, string Lang, string Level)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_Paging", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@CurentPage", CurentPage));
                dbCmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                dbCmd.Parameters.Add(new SqlParameter("@Lang", Lang));
                dbCmd.Parameters.Add(new SqlParameter("@Level", Level));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }

        public List<News> News_Search(string Name, string Tag, string Title, string Date)
        {
            List<News> list = new List<News>();
            using (SqlCommand dbCmd = new SqlCommand("sp_News_Search", Opencon()))
            {
                News obj = new News();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Name", Name));
                dbCmd.Parameters.Add(new SqlParameter("@Tag", Tag));
                dbCmd.Parameters.Add(new SqlParameter("@Title", Title));
                dbCmd.Parameters.Add(new SqlParameter("@Date", Date));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.NewsIDataReader(dr));
                    }
                }
                dr.Close();
                obj = null;
            }
            return list;
        }
       
        public bool News_Insert(News data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_News_Insert", Opencon()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Name", data.Name));
                dbCmd.Parameters.Add(new SqlParameter("@Tag", data.Tag));
                dbCmd.Parameters.Add(new SqlParameter("@Image", data.Image));
                dbCmd.Parameters.Add(new SqlParameter("@File", data.File));
                dbCmd.Parameters.Add(new SqlParameter("@Content", data.Content));
                dbCmd.Parameters.Add(new SqlParameter("@Detail", data.Detail));
                dbCmd.Parameters.Add(new SqlParameter("@Date", data.Date));
                dbCmd.Parameters.Add(new SqlParameter("@Title", data.Title));
                dbCmd.Parameters.Add(new SqlParameter("@Description", data.Description));
                dbCmd.Parameters.Add(new SqlParameter("@Keyword", data.Keyword));
                dbCmd.Parameters.Add(new SqlParameter("@Priority", data.Priority));
                dbCmd.Parameters.Add(new SqlParameter("@Index", data.Index));
                dbCmd.Parameters.Add(new SqlParameter("@Check1", data.Check1));
                dbCmd.Parameters.Add(new SqlParameter("@Check2", data.Check2));
                dbCmd.Parameters.Add(new SqlParameter("@Check3", data.Check3));
                dbCmd.Parameters.Add(new SqlParameter("@Check4", data.Check4));
                dbCmd.Parameters.Add(new SqlParameter("@Check5", data.Check5));
                dbCmd.Parameters.Add(new SqlParameter("@Check6", data.Check6));
                dbCmd.Parameters.Add(new SqlParameter("@Order", data.Order));
                dbCmd.Parameters.Add(new SqlParameter("@Active", data.Active));
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", data.GroupNewsId == "" || data.GroupNewsId == "0" ? DBNull.Value : (object)data.GroupNewsId));
                dbCmd.Parameters.Add(new SqlParameter("@Lang", data.Lang));
                dbCmd.Parameters.Add(new SqlParameter("@Views", data.Views));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("News");
            return true;
        }
       
        public bool News_Update(News data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_News_Update", Opencon()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", data.Id));
                dbCmd.Parameters.Add(new SqlParameter("@Name", data.Name));
                dbCmd.Parameters.Add(new SqlParameter("@Tag", data.Tag));
                dbCmd.Parameters.Add(new SqlParameter("@Image", data.Image));
                dbCmd.Parameters.Add(new SqlParameter("@File", data.File));
                dbCmd.Parameters.Add(new SqlParameter("@Content", data.Content));
                dbCmd.Parameters.Add(new SqlParameter("@Detail", data.Detail));
                dbCmd.Parameters.Add(new SqlParameter("@Date", data.Date));
                dbCmd.Parameters.Add(new SqlParameter("@Title", data.Title));
                dbCmd.Parameters.Add(new SqlParameter("@Description", data.Description));
                dbCmd.Parameters.Add(new SqlParameter("@Keyword", data.Keyword));
                dbCmd.Parameters.Add(new SqlParameter("@Priority", data.Priority));
                dbCmd.Parameters.Add(new SqlParameter("@Index", data.Index));
                dbCmd.Parameters.Add(new SqlParameter("@Check1", data.Check1));
                dbCmd.Parameters.Add(new SqlParameter("@Check2", data.Check2));
                dbCmd.Parameters.Add(new SqlParameter("@Check3", data.Check3));
                dbCmd.Parameters.Add(new SqlParameter("@Check4", data.Check4));
                dbCmd.Parameters.Add(new SqlParameter("@Check5", data.Check5));
                dbCmd.Parameters.Add(new SqlParameter("@Check6", data.Check6));
                dbCmd.Parameters.Add(new SqlParameter("@Order", data.Order));
                dbCmd.Parameters.Add(new SqlParameter("@Active", data.Active));
                dbCmd.Parameters.Add(new SqlParameter("@GroupNewsId", data.GroupNewsId == "" || data.GroupNewsId == "0" ? DBNull.Value : (object)data.GroupNewsId));
                dbCmd.Parameters.Add(new SqlParameter("@Lang", data.Lang));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("News");
            return true;
        }
        
        public bool News_Delete(string Id)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_News_Delete", Opencon()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", Id));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("News");
            return true;
        }
        
		

    }
}
